package com.csair.seam.infrastructure.poi.service.impl;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.alibaba.fastjson.JSON;
import com.csair.seam.infrastructure.poi.annotation.ImportField;
import com.csair.seam.infrastructure.poi.config.ExcelImportConfig;
import com.csair.seam.infrastructure.poi.entity.ExcelImportResult;
import com.csair.seam.infrastructure.poi.entity.ImportFieldEntity;
import com.csair.seam.infrastructure.poi.exception.ExcelException;
import com.csair.seam.infrastructure.poi.factory.ExcelMappingFactory;
import com.csair.seam.infrastructure.poi.service.ExcelImportService;

public class ExcelImportServiceImpl implements ExcelImportService{
	private ExcelImportConfig config;
	
	public ExcelImportServiceImpl(ExcelImportConfig config) {
		this.config = config;
	}

	@Override
	public <T> ExcelImportResult<T> importExcel(InputStream in, Class<T> pojoClass) {
		ExcelImportResult<T> result = new ExcelImportResult<>();
		try(Workbook book = WorkbookFactory.create(in)){
			int lastSheetIndex = config.getImportSheetNum()>0?config.getStartSheetIndex()+config.getImportSheetNum():book.getNumberOfSheets();
			for(int i = config.getStartSheetIndex();i < lastSheetIndex;i++) {
				reaSheet(book.getSheetAt(i),result,pojoClass);
			}
		}catch (Exception e) {
			throw new ExcelException(e);
		}
		return result;
	}
	
	private <T> void reaSheet(Sheet sheet,ExcelImportResult<T> result,Class<T> pojoClass) {
		Map<Integer,ImportFieldEntity> proertyMappingMap = getImportFieldMapping(sheet.getRow(config.getHeaderRowIndex()),pojoClass);
		Iterator<Row> rowIterator = sheet.rowIterator();
		for(int j = 0;j<config.getStartRowIndex();j++) {
			rowIterator.next();
		}
		List<T> successList = new ArrayList<>();
		StringBuilder errorMsgBuilder = new StringBuilder();
		List<Map<String, Object>> fieldValueList = new ArrayList<>();
		while(rowIterator.hasNext()) {
			Row row = rowIterator.next();
			if(checkNullRow(row,proertyMappingMap.size())) {
				continue;
			}
			StringBuilder rowErrorMsg = new StringBuilder();
			Map<String, Object> fieldValueMap = new HashMap<>();
			for (Entry<Integer, ImportFieldEntity> entry : proertyMappingMap.entrySet()) {
				Cell cell = row.getCell(entry.getKey());
				Object value = getCellValue(cell);
				String cellErrorMsg = validateCell(value,entry.getValue());
				if(StringUtils.isNotBlank(cellErrorMsg)) {
					rowErrorMsg.append(cellErrorMsg);
				}
				fieldValueMap.put(entry.getValue().getFieldName(), value);
			}
			String rowErrorMsgString = rowErrorMsg.toString();
			if(StringUtils.isNotBlank(rowErrorMsgString)) {
				errorMsgBuilder.append("第").append(row.getRowNum()+1).append("行：").append(rowErrorMsgString).append("\n");
			}else {
				fieldValueList.add(fieldValueMap);
			}
		}
		for(int j=0;j<fieldValueList.size();j++) {
			T t = JSON.parseObject(JSON.toJSONString(fieldValueList.get(j)), pojoClass);
			successList.add(t);
		}
		result.setSuccessList(successList);
		result.setErrorMsg(errorMsgBuilder.toString());
	}
	
	private boolean checkNullRow(Row row,int columnSize) {
		boolean isNullRow = true;
		for(int i=0;i<columnSize;i++) {
			Cell cell = row.getCell(i);
			Object value = getCellValue(cell);
			if(value !=null && StringUtils.isNotBlank(String.valueOf(value))) {
				isNullRow = false;
				break;
			}
		}
		return isNullRow;
	}
	
	private Map<Integer,ImportFieldEntity> getImportFieldMapping(Row headerRow,Class<?> pojoClass){
		Map<String, Integer> headerMap = getImportExcelHeaderMap(headerRow);
		Map<String, ImportFieldEntity> fieldMap = ExcelMappingFactory.loadImportFieldClass(pojoClass);
		Map<Integer,ImportFieldEntity> propertyMappingMap = new HashMap<>();
		for (Entry<String, ImportFieldEntity> entry : fieldMap.entrySet()) {
			if(!headerMap.containsKey(entry.getKey())) {
				throw new ExcelException("导入的Excel文件中没有【name=%s】列", entry.getKey());
			}
			propertyMappingMap.put(headerMap.get(entry.getKey()), entry.getValue());
		}
		return propertyMappingMap;
	}
	
	private Map<String, Integer> getImportExcelHeaderMap(Row headerRow) {
		Map<String, Integer> headerMap = new HashMap<>();
		Iterator<Cell> cellIterator = headerRow.cellIterator();
		while(cellIterator.hasNext()) {
			Cell cell = cellIterator.next();
			String value = cell.getStringCellValue();
			if(StringUtils.isBlank(value)) {
				throw new ExcelException("导入的Excel文件中存在空标题表头");
			}
			if(headerMap.containsKey(value)) {
				throw new ExcelException("导入的Excel文件中【%s】列重复",value);
			}
			headerMap.put(value, cell.getColumnIndex());
		}
		return headerMap;
	}
	
	private Object getCellValue(Cell cell) {
		if(cell == null || cell.getCellType() == CellType.BLANK) {
			return null;
		}
    	Object result = null;
    	if(cell.getCellType() == CellType.STRING) {
    		result = cell.getStringCellValue();
    	}else if(cell.getCellType() == CellType.BOOLEAN) {
    		result = cell.getBooleanCellValue();
    	}else if(cell.getCellType() == CellType.NUMERIC) {
    		if(DateUtil.isCellDateFormatted(cell)) {
    			result = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
    		}else {
    			result = new DecimalFormat("#.######").format(cell.getNumericCellValue());
    		}
    	}
        return result;
    }
	
	private String validateCell(Object cellValue,ImportFieldEntity importFieldEntity) {
		ImportField importField = importFieldEntity.getImportField();
		if(cellValue == null && importField.required()) {
			return String.format("导入的Excel文件中【%s】列，值不能为空",importField.name());
		}
		if(cellValue == null) {
			return null;
		}
		String value = String.valueOf(cellValue);
		if("String".equals(importFieldEntity.getSimpleJavaType())) {
			return validateStringValue(value,importField);
		}else if("Date".equals(importFieldEntity.getSimpleJavaType())){
			return validateDateValue(value,importField);
		}else{
			return validateNumericValue(value,importFieldEntity);
		}
	}
	
	private String validateStringValue(String value,ImportField excelProperty) {
		StringBuilder errorString = new StringBuilder();
		if(excelProperty.required() && StringUtils.isBlank(value)) {
			errorString.append(String.format("导入的Excel文件中【%s】值不能为空;",excelProperty.name()));
		}
		if(excelProperty.minLength() > 0 && value.length() < excelProperty.minLength()) {
			errorString.append(String.format("导入的Excel文件中【%s】值最小长度为%d;",excelProperty.name(),excelProperty.minLength()));
		}
		if(excelProperty.maxLength() > 0 && value.length() > excelProperty.maxLength()) {
			errorString.append(String.format("导入的Excel文件中【%s】值最大长度为%s;",excelProperty.name(),excelProperty.maxLength()));
		}
		if(StringUtils.isNotBlank(excelProperty.regex())){
			Pattern pattern = Pattern.compile(excelProperty.regex());
			if(!pattern.matcher(value).matches()) {
				errorString.append(excelProperty.regexMessage()).append(";");
			}
		}
		return errorString.toString();
	}
	
	private String validateNumericValue(String value,ImportFieldEntity importFieldEntity) {
		ImportField excelProperty = importFieldEntity.getImportField();
		Pattern pattern = Pattern.compile("^\\d+(\\.\\d+)?$");
		if(!pattern.matcher(value).matches()) {
			return String.format("导入的Excel文件中【%s】列非数值类型，实际值：%s;", excelProperty.name(),value);
		}
		
		StringBuilder errorString = new StringBuilder();
		boolean isIntType = ("int".equals(importFieldEntity.getSimpleJavaType()) || "Integer".equals(importFieldEntity.getSimpleJavaType()));
		if(StringUtils.isNotBlank(excelProperty.min())) {
			boolean isError = false;
			if(isIntType) {
				if(value.length() > 9)
					isError = true;
				else
					isError = Integer.parseInt(value) < Integer.parseInt(excelProperty.min());
			}else {
				if(value.length() > 9)
					isError = true;
				else
					isError = Double.parseDouble(value) < Double.parseDouble(excelProperty.min());
			}
			if(isError) {
				errorString.append(String.format("导入的Excel文件中【%s】列最小值:%s，实际值：%s;", excelProperty.name(),excelProperty.min(),value));
			}
		}
		if(StringUtils.isNotBlank(excelProperty.max())) {
			boolean isError = false;
			if(isIntType) {
				if(value.length() > 9)
					isError = true;
				else
					isError = Integer.parseInt(value) > Integer.parseInt(excelProperty.max());
			}else {
				if(value.length() > 9)
					isError = true;
				else
					isError = Double.parseDouble(value) > Double.parseDouble(excelProperty.max());
			}
			if(isError) {
				errorString.append(String.format("导入的Excel文件中【%s】列，最大值：%s，实际值：%s;", excelProperty.name(),excelProperty.max(),value));
			}
		}
		return errorString.toString();
	}
	
	private String validateDateValue(String value,ImportField excelProperty) {
		try {
			SimpleDateFormat sdf1 = new SimpleDateFormat(excelProperty.dateFormat());
			sdf1.parse(value);
			return null;
		} catch (Exception e) {
			return String.format("导入的Excel文件中【%s】列日期值设置错误，实际值：%s;", excelProperty.name(),value);
		}
	}
}
